package org.ocean.online.web.workspace.inner;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.ocean.hibernate.CreateTableUtil;
import org.ocean.online.constant.Constant;
import org.ocean.online.sys.DbGuard;
import org.ocean.online.utils.ModelUtil;
import org.springframework.transaction.TransactionStatus;

public class DesignModelService implements Serializable{

	public void deleteModel(DbGuard db,String modelName){
		String drop = "drop table  if exists "+modelName;
		String delete = "delete from model where modelName=?";
		db.execute(drop);
		db.execute(delete, modelName);
	}
	
	public void rename(DbGuard db,String projectName,String modelName,String newModelName) {
		String update="update model set modelName=? where projectName=? and modelName=?";
		//TODO change to ?
		String alter="alter table "+modelName+" rename to "+newModelName;
		db.execute(alter);
		db.execute(update,newModelName,projectName,modelName);
		
	}
	public List<Map> queryModel(DbGuard db,String modelName) {
		List<Map> list = db.query("select * from model where 'modelName'=?",modelName);
		return list;
	}
	
	public List<Map> queryModels(DbGuard db,String projectName) {
		List<Map> list = db.query("select 'modelName' from model where \"projectName\"=? group by \"modelName\" ",projectName);
		return list;
	}
	
	public void saveTable(DbGuard db,String projectName,String tableName,String data){
		String[] fileds = data.split("#");
		List<Map> columns = new ArrayList<Map>();
		boolean hasUID=false;
		for(String field: fileds){
			String[] attrs = field.split(";");
			Map<String,String> column = new HashMap<String,String>();
			for(String attr : attrs){
				String[] pair = attr.split(":");
				if(pair.length<2){
					column.put(pair[0], "");
				}else{
					column.put(pair[0], pair[1]);
				}
			}
			if("uid".equals(column.get(Constant.AttrName_VariableName))){
				hasUID=true;
			}
			columns.add(column);
		}
		if(hasUID==false){
			Map uid = new HashMap();
			uid.put(Constant.AttrName_VariableName, "uid");
			uid.put(Constant.AttrLength_VariableName, 50);
			uid.put(Constant.AttrType_VariableName, "String");
			uid.put(Constant.Commnent_VariableName, "");
			columns.add(uid);
		}
		
		ModelUtil.validate(columns);
//		System.out.println(fields);
		List<Map> oldCols =getColumns(db,tableName);
		if(!oldCols.isEmpty()){
			// modify table
			List<Map> addCols = ModelUtil.generateAddColumnSql(columns, oldCols);
			List<String> addSql = CreateTableUtil.getAddColSql(tableName, addCols);
			List<Map> dropCols = ModelUtil.generateDropColumns(columns, oldCols);
			List<Map> alterCols = ModelUtil.generateAlterColumns(columns, oldCols); 
			dropCols =removeUID(dropCols);
			alterCols =removeUID(alterCols);
			List<String> dropSql = CreateTableUtil.getDropColSql(tableName,dropCols);
			List<String> alterSql = CreateTableUtil.getAlterColSql(tableName,alterCols);
			List<String> all = new ArrayList<String>();
			all.addAll(addSql);
			all.addAll(dropSql); 
			all.addAll(alterSql);
			TransactionStatus status=null; 
			try{
				status = db.getTransactionManager().getTransaction(null);
				for(String sql : all){
					db.execute(sql);
				}
				String delete = "delete from model where 'modelName'=?";
				db.execute(delete,tableName);
				for(Map<String,String> row : columns){
					row.put("projectName", projectName);
					row.put("modelName", tableName);
					if(StringUtils.isEmpty(row.get("uid"))){
						row.put("uid", UUID.randomUUID().toString());
					}
					db.insert("model", row);
				}
				db.getTransactionManager().commit(status);
			}
			catch(RuntimeException ex){
				db.getTransactionManager().rollback(status);
				throw ex;
			}
			return;
		}
		
		// create table
		String createSql = CreateTableUtil.getCreateTableSql(tableName, columns);
		TransactionStatus status = null;
		try{
				status = db.getTransactionManager().getTransaction(null);
				String dropSql = "drop table if exists "+tableName;
				db.execute(dropSql);
				String delete = "delete from model where 'projectName'=? and 'modelName'=?";
				db.execute(delete,projectName,tableName);
				db.execute(createSql);
				for(Map<String,String> row : columns){
					row.put("projectName", projectName);
					row.put("modelName", tableName);
					row.put("uid", UUID.randomUUID().toString());
					db.insert("model", row);
				}
				db.getTransactionManager().commit(status);
			}
		catch(RuntimeException ex){
			db.getTransactionManager().rollback(status);
			throw ex;
		}
	}

	private List<Map> removeUID(List<Map> dropCols) {
		List<Map> result = new ArrayList<Map>();
		for(Map map : dropCols){
			if(map.get(Constant.AttrName_VariableName).equals("uid")){
				continue;
			}
			result.add(map);
		}
		return result;
	}

	private List<Map> getColumns(DbGuard db,String tableName) {
		String sql = "select * from model where 'modelName'=?";
		List<Map> list = db.query(sql, tableName);
		return list;
	}
}
